package com.gitee.zycra.utils;

import com.gitee.zycra.model.Column;
import com.gitee.zycra.model.GeneratorConfig;
import org.apache.maven.plugin.MojoFailureException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author zycra
 */
public final class DbUtils {

    private DbUtils() {
    }

    public static Map<String, String> selectTableComment(GeneratorConfig config) throws MojoFailureException {
        String database = config.getDatabase();
        List<String> tableList = config.getTableList();
        Map<String, String> result = new HashMap<>(tableList.size());
        try (Connection connection = getConnection(config)) {
            for (String tableName : tableList) {
                List<Map<String, Object>> allColumns = executeSql(connection, selectTableCommentSql(database, tableName));
                result.put(tableName, String.valueOf(allColumns.getFirst().get("TABLE_COMMENT")));
            }
        } catch (Exception e) {
            throw new MojoFailureException("查询表备注异常", e);
        }
        return result;
    }

    public static Map<String, List<Column>> selectAllColumns(GeneratorConfig config) throws MojoFailureException {
        String database = config.getDatabase();
        List<String> tableList = config.getTableList();
        Map<String, List<Column>> result = new HashMap<>(tableList.size());
        List<Column> columnList = null;
        try (Connection connection = getConnection(config)) {
            for (String tableName : tableList) {
                List<Map<String, Object>> allColumns = executeSql(connection, selectAllColumnSql(database, tableName));
                columnList = new ArrayList<>();
                for (Map<String, Object> column : allColumns) {
                    String dataType = String.valueOf(column.get("DATA_TYPE"));
                    String columnName = String.valueOf(column.get("COLUMN_NAME"));
                    columnList.add(new Column().setDataType(getDataType(dataType))
                            .setJdbcType(getJdbcType(dataType))
                            .setColumnComment(String.valueOf(column.get("COLUMN_COMMENT")))
                            .setColumnName(columnName)
                            .setColumnDefault(column.get("COLUMN_DEFAULT") == null ? null : column.get("COLUMN_DEFAULT").toString()));
                }
                result.put(tableName, columnList);
            }
        } catch (Exception e) {
            throw new MojoFailureException("查询列名异常", e);
        }
        return result;
    }

    public static Map<String, String> selectPrimaryKeyColumnMap(GeneratorConfig config) throws MojoFailureException {
        String database = config.getDatabase();
        List<String> tableList = config.getTableList();
        Map<String, String> result = new HashMap<>();
        try (Connection connection = getConnection(config)) {
            for (String tableName : tableList) {
                List<Map<String, Object>> primaryKeyList = executeSql(connection, primaryKeySql(database, tableName));
                result.put(tableName, String.valueOf(primaryKeyList.getFirst().get("COLUMN_NAME")));
            }
        } catch (Exception e) {
            throw new MojoFailureException("查询主键列异常", e);
        }
        return result;
    }

    public static Map<String, Map<String, List<String>>> selectUniqueKeyColumnMap(GeneratorConfig config) throws MojoFailureException {
        String database = config.getDatabase();
        List<String> tableList = config.getTableList();
        Map<String, Map<String, List<String>>> result = new HashMap<>();
        try (Connection connection = getConnection(config)) {
            for (String tableName : tableList) {
                List<Map<String, Object>> uniqueKeyList = executeSql(connection, uniqueKeyGroupSql(database, tableName));
                Map<String, List<String>> keyColumnMap = new HashMap<>();
                for (Map<String, Object> uk : uniqueKeyList) {
                    String indexName = String.valueOf(uk.get("INDEX_NAME"));
                    if (!keyColumnMap.containsKey(indexName)) {
                        keyColumnMap.put(indexName, new ArrayList<>());
                    }
                    keyColumnMap.get(indexName).add(String.valueOf(uk.get("COLUMN_NAME")));
                }
                result.put(tableName, keyColumnMap);
            }
        } catch (Exception e) {
            throw new MojoFailureException("查询唯一索引列异常", e);
        }
        return result;
    }

    private static Connection getConnection(GeneratorConfig config) throws MojoFailureException {
        String database = config.getDatabase();
        List<String> tableList = config.getTableList();
        if (database == null || tableList == null || tableList.isEmpty()) {
            return null;
        }
        try {
            return DriverManager.getConnection(config.getUrl(), config.getUsername(), config.getPassword());
        } catch (Exception e) {
            throw new MojoFailureException("获取连接异常", e);
        }
    }

    private static List<Map<String, Object>> executeSql(Connection connection, String sql) throws MojoFailureException {
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<String> columnLabelList = new ArrayList<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                columnLabelList.add(metaData.getColumnLabel(i));
            }
            List<Map<String, Object>> result = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, Object> row = new HashMap<>();
                for (String columnLabel : columnLabelList) {
                    row.put(columnLabel, resultSet.getObject(columnLabel));
                }
                result.add(row);
            }
            return result;
        } catch (SQLException e) {
            throw new MojoFailureException("执行SQL异常", e);
        }
    }

    private static String selectTableCommentSql(String database, String tableName) {
        return "SELECT t.TABLE_COMMENT FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = '"
                + database + "' AND t.TABLE_NAME = '" + tableName + "'";
    }

    private static String selectAllColumnSql(String database, String tableName) {
        return "SELECT c.COLUMN_NAME, c.DATA_TYPE, c.COLUMN_COMMENT, c.COLUMN_DEFAULT FROM information_schema.COLUMNS c WHERE c.TABLE_SCHEMA = '"
                + database + "' AND c.TABLE_NAME = '" + tableName + "'" + " ORDER BY c.ORDINAL_POSITION";
    }

    private static String primaryKeySql(String database, String tableName) {
        return "SELECT st.COLUMN_NAME FROM information_schema.STATISTICS st WHERE st.TABLE_SCHEMA = '" + database + "' AND st"
                + ".TABLE_NAME = '" + tableName + "' AND st.INDEX_NAME = 'PRIMARY' AND st.NON_UNIQUE = 0";
    }

    private static String uniqueKeyGroupSql(String database, String tableName) {
        return "SELECT st.INDEX_NAME, st.COLUMN_NAME FROM information_schema.STATISTICS st WHERE st.TABLE_SCHEMA = '" + database + "' AND st"
                + ".TABLE_NAME = '" + tableName + "' AND st.INDEX_NAME != 'PRIMARY' AND st.NON_UNIQUE = 0 ORDER BY st.INDEX_NAME,st.SEQ_IN_INDEX";
    }

    private static String getDataType(String dataType) {
        switch (dataType) {
            case "bigint":
                return "Long";
            case "binary":
            case "blob":
            case "longblob":
            case "mediumblob":
            case "tinyblob":
            case "varbinary":
                return "byte[]";
            case "bit":
            case "bool":
            case "boolean":
                return "Boolean";
            case "char":
            case "enum":
            case "json":
            case "longtext":
            case "mediumtext":
            case "set":
            case "text":
            case "tinytext":
            case "varchar":
                return "String";
            case "date":
            case "year":
                return "LocalDate";
            case "datetime":
                return "LocalDateTime";
            case "timestamp":
                return "Timestamp";
            case "decimal":
                return "BigDecimal";
            case "double":
            case "numeric":
            case "real":
                return "Double";
            case "float":
                return "Float";
            case "int":
            case "mediumint":
            case "smallint":
            case "tinyint":
                return "Integer";
            case "time":
                return "LocalTime";
            default:
                return null;
        }
    }

    private static String getJdbcType(String dataType) {
        switch (dataType) {
            case "bigint":
                return "BIGINT";
            case "binary":
                return "BINARY";
            case "blob":
            case "longblob":
            case "mediumblob":
                return "LONGVARBINARY";
            case "tinyblob":
            case "varbinary":
                return "VARBINARY";
            case "json":
            case "longtext":
            case "mediumtext":
            case "text":
                return "LONGVARCHAR";
            case "bit":
            case "bool":
            case "boolean":
                return "BIT";
            case "set":
            case "char":
            case "enum":
                return "CHAR";
            case "tinytext":
            case "varchar":
                return "VARCHAR";
            case "date":
            case "year":
                return "DATE";
            case "datetime":
            case "timestamp":
                return "TIMESTAMP";
            case "decimal":
            case "numeric":
                return "DECIMAL";
            case "double":
                return "DOUBLE";
            case "real":
                return "REAL";
            case "float":
                return "FLOAT";
            case "int":
            case "mediumint":
                return "INTEGER";
            case "smallint":
                return "SMALLINT";
            case "tinyint":
                return "TINYINT";
            case "time":
                return "TIME";
            default:
                return "OTHER";
        }
    }
}
